/**
 *	mysql数据库操作方法类
 *	开发者：雨中磐石
 *	地址：www.rockoa.com
 *	日期：2023-07-17
 */

package com.framerock.db;

import com.framerock.baselib.AA;
import com.framerock.baselib.CLog;
import com.framerock.baselib.CallBack;
import com.framerock.baselib.Config;
import com.framerock.baselib.Rock;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


public class DBConnMySql extends DBConn {


    private Connection connEction   =  null;
    private Statement statementM    =  null;


    /**
     * 连接自动换行快捷键：Alt+Shift+Y
     * */
    private void connection(Boolean bo) {
        if(connEction!=null)return;
        String user = Config.getConfig("db.user");
        try {
            connEction = DriverManager.getConnection("jdbc:mysql://"+ Config.getConfig("db.host")+"/"+ DB.DBBASE+"?useUnicode=true&characterEncoding=utf-8&useSSL=false", user, Config.getConfig("db.pass"));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            connEction = null;
            String str = this.addError(throwables.toString());
            CLog.print("mysql error "+str+"");
            if(bo && Rock.contain(str, "Unknown database '"+DB.DBBASE+"'") && user.equals("root"))createbase();//不存在就创建
        }
    }
    private void connection() {
    	connection(true);
    }
    
    /**
     * 数据库不存在就创建
     * */
    private void createbase(){
    	Connection connbase;
    	try {
    		connbase = DriverManager.getConnection("jdbc:mysql://"+ Config.getConfig("db.host")+"/information_schema?useUnicode=true&characterEncoding=utf-8", Config.getConfig("db.user"), Config.getConfig("db.pass"));
    		String sql = "CREATE DATABASE `"+DB.DBBASE+"` DEFAULT CHARACTER SET "+DB.CHARSET+" COLLATE "+DB.CHARSET+"_general_ci";
    		Statement statement = connbase.createStatement();
            Boolean bo = statement.execute(sql);
            statement.close(); connbase.close(); connbase = null;
            if(bo)connection(false);//创建成功重新连接数据库
            errorString = "";
    		CLog.print("create DATABASE("+DB.DBBASE+") ok");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            String str = this.addError(throwables.toString());
            CLog.print("mysql createbaseerror("+DB.DBBASE+"):"+str+"");
        }
    }


    @Override
    public void close()
    {
        if(connEction!=null){
            try {
                if(statementM!=null)statementM.close();
                connEction.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            connEction = null;
        }
    }

    @Override
    public Boolean Query(String sql)
    {
        this.errorString = "";
        connection();
        if(connEction==null) return false;
        try {
            Statement statement = connEction.createStatement();
            DB.NOWSQL  = sql;
            statement.execute(sql);
            statement.close();
            return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            CLog.debug(sql);
            this.addError(throwables.toString());
        }
        return false;
    }


    @Override
    public int insert(String table, Map<String, String> map)
    {
        Boolean bo = this.insertbo(table, map);
        int id  = 0;
        if(bo) {
            Map<String,String> rs = getOne(table, "1=1", "max(`id`) as `id`");
            if(rs!=null) {
                id = Integer.parseInt(Rock.getMapString(rs, "id", "0"));
            }
        }
        return id;
    }

    @Override
    public Boolean insertbo(String table, Map<String, String> map)
    {
        String sql = "INSERT INTO "+this.chuliTable(table)+" SET ";
        sql+=getsqlVal(map);
        Boolean bo = this.Query(sql);
        return bo;
    }

    @Override
    public Boolean update(String table, Map<String, String> map, String where)
    {
        return update(table, getsqlVal(map), where);
    }

    @Override
    public Boolean update(String table, String upcont, String where)
    {
        String sql = "UPDATE "+this.chuliTable(table)+" SET "+upcont+"";
        sql+=" WHERE "+DB.chuliWhere(where)+"";
        return this.Query(sql);
    }

    @Override
    public Boolean delete(String table, String where)
    {
        String sql = "DELETE FROM "+this.chuliTable(table)+" WHERE "+DB.chuliWhere(where)+"";
        return this.Query(sql);
    }

    /**
     * 执行
     * */
    public ResultSet queryResult(String sql)
    {
        this.connection();
        if(connEction==null) return null;
        ResultSet rows = null;
        DB.NOWSQL = sql;
        try {
            if(statementM!=null)statementM.close();
            statementM = connEction.createStatement();
            rows = statementM.executeQuery(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            CLog.error(sql);
            this.addError(throwables.toString());
        }
        return rows;
    }

    @Override
    public Map<String,String> getOne(String sql)
    {
        ResultSet rs = queryResult(sql),nrs = null;
        if(rs==null)return null;
        try {
            if(rs.next()) nrs = rs;
            if(nrs!= null) {
                Map<String,String> da = Rock.getMap();
                ResultSetMetaData rsmd = rs.getMetaData();
                String val,typ;
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    typ = rsmd.getColumnTypeName(i);
                    val = nrs.getString(i);
                    if(typ.equals("DATETIME") && val !=null && val.length()==21)val = val.substring(0, 19);
                    da.put(rsmd.getColumnLabel(i), val);
                }
                return da;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            this.addError(throwables.toString());
        }
        return null;
    }

    @Override
    public Map<String,String> getOne(String table, String where)
    {
        return getOne(table, where, "*");
    }

    @Override
    public Map<String,String> getOne(String table, String where, String fields)
    {
        String sql = "SELECT "+DB.chuliFields(fields)+" FROM "+this.chuliTable(table)+" WHERE "+DB.chuliWhere(where)+" LIMIT 1";
        return getOne(sql);
    }

    @Override
    public ArrayList<Map<String, String>> getAll(String sql, CallBack call)
    {
        ResultSet rows = queryResult(sql),nrs;
        if(rows==null)return null;
        List<String> farr = new ArrayList<String>(),fart = new ArrayList<String>();
        ArrayList<Map<String, String>> data = Rock.getMapList();
        try {
            int i,len;
            ResultSetMetaData rsmd = rows.getMetaData();
            len = rsmd.getColumnCount();
            for (i = 1; i <= len; i++){
                farr.add(rsmd.getColumnLabel(i));
                fart.add(rsmd.getColumnTypeName(i));
            }
            Map<String,String> da;String val,typ;
            while (rows.next()){
                nrs = rows; da  = Rock.getMap();
                for (i=0; i< len;i++){
                    val = nrs.getString(i+1);
                    typ = fart.get(i);
                    if(typ.equals("DATETIME") && val !=null && val.length()==21)val = val.substring(0, 19);
                    da.put(farr.get(i), val);
                }
                if(call != null)da = call.backMap(da);
                data.add(da);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            this.addError(throwables.toString());
        }
        return data;
    }

    @Override
    public ArrayList<Map<String, String>> getAll(String sql)
    {
        return getAll(sql, null);
    }

    @Override
    public int getCount(String table, String where)
    {
        int xu = 0;
        Map<String,String> rs = getOne(table, where, "count(1) as `count`");
        if(rs!=null)xu = Integer.parseInt(Rock.getMapString(rs, "count", "0"));
        return xu;
    }

    @Override
    public ArrayList<Map<String, String>> getColumns(String table)
    {
        ArrayList<Map<String, String>> rows = this.getAll("show full fields FROM "+this.chuliTable(table)+"", new CallBack(){
            public Map<String, String> backMap(Map<String, String> a) {
                String dev = a.get("Default");
                String sm = a.get("Comment");
                Map<String, String> da = Rock.getMap();
                da.put("fields", a.get("Field"));
                da.put("type", a.get("Type").toLowerCase());
                da.put("sm", sm);
                da.put("dev", dev);
                return da;
            }
        });
        return rows;
    }

    @Override
    public String[] getAlltable(String base)
    {
        String barr[] = new String[]{};
        AA.VARCHAR  = "";
        String sql = "SELECT `TABLE_NAME` FROM information_schema.`TABLES` WHERE `TABLE_SCHEMA`='"+base+"'";
        ArrayList<Map<String, String>> rows = this.getAll(sql, new CallBack(){
            public Map<String, String> backMap(Map<String, String> a) {
                AA.VARCHAR+=","+a.get("TABLE_NAME")+"";
                return a;
            }
        });
        String str = AA.VARCHAR;
        if(str.length()>0)barr = str.substring(1).split(",");
        return barr;
    }

    @Override
    public String[] getAlltable(){return getAlltable(DB.DBBASE);}
}
